<?php
class CalculationsController extends AppController {
    public function index()
    {
        include("Component/connection.php");
        set_time_limit(0);

        $totalInsert=$this->Session->read("totalInsert");
        $totalUpdate=$this->Session->read("totalUpdate");
        $calculationType=$this->Session->read("ulType");
        $nameArray=array();
        $dateArray=array();
        //$con=mysqli_query("localhost","root","","dfs");

        //get all record name and date
        if($totalInsert!=0)
        {
            for($n=1;$n<=$totalInsert;$n++)
            {
                array_push($nameArray,$this->Session->read("insertName[".$n."]"));
                array_push($dateArray,$this->Session->read("insertDate[".$n."]"));
            }
        }
        if ($totalUpdate!=0)
        {
            for($n=1;$n<=$totalUpdate;$n++)
            {
                array_push($nameArray,$this->Session->read("updateName[".$n."]"));
                array_push($dateArray,$this->Session->read("updateDate[".$n."]"));
            }
        }
            
            //get all portfolio name and date which need to be updated
            //store in a two dimension array array(array(name,date1,date2,date3....),array(name,date1,date2,date3....).....)
            $portfolioNameDate=array();
            $rpmNameDate=array();
            $arrayTemp=array();
            if($calculationType=="Fund")
            {
                for($sn=0;$sn<sizeof($nameArray);$sn++)
                {
                    $sqlsn="select distinct portfolioname from modelportfolios where fundslist like '%".$nameArray[$sn]."%' and year(date)=year('".$dateArray[$sn]."') and month(date)=month('".$dateArray[$sn]."')";
                    $resulttemp=mysqli_query($con,$sqlsn);
                    foreach ($resulttemp as $rt)
                    {
                        //$r is a unique portfolio name 
                        foreach ($rt as $r)
                        {
                            $arrayTemp=array();
                            $sqldate="select date from modelportfolios where portfolioname='".$r."' and year(date)='".$dateArray[$sn]."' and month(date)>=month('".$dateArray[$sn]."') order by date asc";
                            $sqldate2="select date from modelportfolios where portfolioname='".$r."' and year(date)>'".$dateArray[$sn]."' order by date asc";
                            $resulttempdate=mysqli_query($con,$sqldate);
                            $resulttempdate2=mysqli_query($con,$sqldate2);
                            array_push($arrayTemp,$r);
                            foreach($resulttempdate as $rtd)
                            {
                                foreach($rtd as $rd)
                                {
                                    array_push($arrayTemp,$rd);
                                }
                            }
                            foreach($resulttempdate2 as $rtd)
                            {
                                foreach($rtd as $rd)
                                {
                                    array_push($arrayTemp,$rd);
                                }
                            }
                            array_push($portfolioNameDate,$arrayTemp);

                            $arrayTemp2=array();
                            $sqldate="select DISTINCT portfolioname from modelportfolios where fundslist like '%".$r."%' and year(date)='".$dateArray[$sn]."' and month(date)>=month('".$dateArray[$sn]."') order by date asc";
                            $sqldate2="select DISTINCT portfolioname from modelportfolios where fundslist like '%".$r."%' and year(date)>'".$dateArray[$sn]."' order by date asc";
                            $resulttempdate=mysqli_query($con,$sqldate);
                            $resulttempdate2=mysqli_query($con,$sqldate2);
                            array_push($arrayTemp,$r);
                            foreach($resulttempdate as $rtd)
                            {
                                foreach($rtd as $rd)
                                {
                                    array_push($arrayTemp2,$rd);
                                }
                            }
                            foreach($resulttempdate2 as $rtd)
                            {
                                foreach($rtd as $rd)
                                {
                                    if(!in_array($rd,$arrayTemp2))
                                    {
                                        array_push($arrayTemp2,$rd);
                                    }
                                }
                            }

                            foreach($arrayTemp2 as $name)
                            {
                                $arrayTemp3=array();
                                array_push($arrayTemp3, $name);
                                $sqldate="select date from modelportfolios where portfolioname='".$name."' and year(date)='".$dateArray[$sn]."' and month(date)>=month('".$dateArray[$sn]."') order by date asc";
                                $sqldate2="select date from modelportfolios where portfolioname='".$name."' and year(date)>'".$dateArray[$sn]."' order by date asc";
                                $resulttempdate=mysqli_query($con,$sqldate);
                                $resulttempdate2=mysqli_query($con,$sqldate2);
                                foreach($resulttempdate as $rtd)
                                {
                                     foreach($rtd as $rd)
                                    {
                                        array_push($arrayTemp3,$rd);
                                    }
                                }

                                foreach($resulttempdate2 as $rtd)
                                {
                                    foreach($rtd as $rd)
                                    {
                                        array_push($arrayTemp3,$rd);
                                    }
                                }
                                array_push($rpmNameDate,$arrayTemp3);
                            }
                        }
                    }
                }
            }
            else if ($calculationType=="Index")
            {
                for($sn=0;$sn<sizeof($nameArray);$sn++)
                {
                    $sqlsn="select distinct portfolioname from modelportfolios where indexlist like '%".$nameArray[$sn]."%' and year(date)=year('".$dateArray[$sn]."') and month(date)=month('".$dateArray[$sn]."')";
                    $resulttemp=mysqli_query($con,$sqlsn);
                    foreach ($resulttemp as $rt)
                    {
                        //$r is a unique portfolio name 
                        foreach ($rt as $r)
                        {
                            $arrayTemp=array();
                            $sqldate="select date from modelportfolios where portfolioname='".$r."' and year(date)='".$dateArray[$sn]."' and month(date)>=month('".$dateArray[$sn]."') order by date asc";
                            $sqldate2="select date from modelportfolios where portfolioname='".$r."' and year(date)>'".$dateArray[$sn]."' order by date asc";
                            $resulttempdate=mysqli_query($con,$sqldate);
                            $resulttempdate2=mysqli_query($con,$sqldate2);
                            array_push($arrayTemp,$r);
                            foreach($resulttempdate as $rtd)
                            {
                                foreach($rtd as $rd)
                                {
                                    array_push($arrayTemp,$rd);
                                }
                            }
                            foreach($resulttempdate2 as $rtd)
                            {
                                foreach($rtd as $rd)
                                {
                                    array_push($arrayTemp,$rd);
                                }
                            }
                            array_push($portfolioNameDate,$arrayTemp);
                        }
                    }
                }
            }
            //calculations for funds
            if($calculationType=="Fund")
            {
                //echo "namearray:".$arraylength." ";
                for($n=0;$n<sizeof($nameArray);$n++)
                {
                    $resultbarray=array();
                    $name=$nameArray[$n];
                    $date=$dateArray[$n];
                    $sqlbefore="select fundsid from managedfunds where date<'".$date."' and fundname='".$name."'";
                    $resultbefore=mysqli_query($con,$sqlbefore);
                    foreach ($resultbefore as $rb)
                    {
                        foreach ($rb as $r)
                        {
                            array_push($resultbarray,$r);
                        }
                    }
                    $numberbefore=sizeof($resultbarray);
                    //calc 1 month return
                    if($numberbefore==0){}
                    else if($numberbefore<2)
                    {
                        $onemonthreturn=$this->calcreturnFund($name,$date,1);
                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $sinceinception=$this->calcreturnFund($name,$date,'sinceinception');
                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3 months
                    else if ($numberbefore<5)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;

                        $onemonthreturn=$this->calcreturnFund($name,$date,1);
                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnFund($name,$date,3);
                        $sinceinception=$this->calcreturnFund($name,$date,'sinceinception');

                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months
                    else if ($numberbefore<11)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;

                        $onemonthreturn=$this->calcreturnFund($name,$date,1);
                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnFund($name,$date,3);
                        $sixmonthreturn=$this->calcreturnFund($name,$date,6);
                        $sinceinception=$this->calcreturnFund($name,$date,'sinceinception');

                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months and 1 year
                    else if ($numberbefore<23)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;
                        $oneyearreturn=0;

                        $onemonthreturn=$this->calcreturnFund($name,$date,1);
                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnFund($name,$date,3);
                        $sixmonthreturn=$this->calcreturnFund($name,$date,6);
                        $oneyearreturn=$this->calcreturnFund($name,$date,12);
                        $sinceinception=$this->calcreturnFund($name,$date,'sinceinception');
                        $sinceinceptiona=$this->calcreturnFund($name,$date,'sinceinceptiona');

                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months and 1,2 year
                    else if ($numberbefore<35)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;
                        $oneyearreturn=0;
                        $twoyearreturn=0;

                        $onemonthreturn=$this->calcreturnFund($name,$date,1);
                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnFund($name,$date,3);
                        $sixmonthreturn=$this->calcreturnFund($name,$date,6);
                        $oneyearreturn=$this->calcreturnFund($name,$date,12);
                        $twoyearreturn=$this->calcreturnFund($name,$date,24);
                        $sinceinception=$this->calcreturnFund($name,$date,'sinceinception');
                        $sinceinceptiona=$this->calcreturnFund($name,$date,'sinceinceptiona');

                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months and 1,2,3 year
                    else if ($numberbefore<47)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;
                        $oneyearreturn=0;
                        $twoyearreturn=0;
                        $threeyearreturn=0;

                        $onemonthreturn=$this->calcreturnFund($name,$date,1);
                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnFund($name,$date,3);
                        $sixmonthreturn=$this->calcreturnFund($name,$date,6);
                        $oneyearreturn=$this->calcreturnFund($name,$date,12);
                        $twoyearreturn=$this->calcreturnFund($name,$date,24);
                        $threeyearreturn=$this->calcreturnFund($name,$date,36);
                        $sinceinception=$this->calcreturnFund($name,$date,'sinceinception');
                        $sinceinceptiona=$this->calcreturnFund($name,$date,'sinceinceptiona');

                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months and 1,2,3,4 year
                    else if ($numberbefore<59)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;
                        $oneyearreturn=0;
                        $twoyearreturn=0;
                        $threeyearreturn=0;
                        $fouryearreturn=0;

                        $onemonthreturn=$this->calcreturnFund($name,$date,1);
                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnFund($name,$date,3);
                        $sixmonthreturn=$this->calcreturnFund($name,$date,6);
                        $oneyearreturn=$this->calcreturnFund($name,$date,12);
                        $twoyearreturn=$this->calcreturnFund($name,$date,24);
                        $threeyearreturn=$this->calcreturnFund($name,$date,36);
                        $fouryearreturn=$this-> calcreturnFund($name,$date,48);
                        $sinceinception=$this->calcreturnFund($name,$date,'sinceinception');
                        $sinceinceptiona=$this->calcreturnFund($name,$date,'sinceinceptiona');

                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months and 1,2,3,4,5 year
                    else
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;
                        $oneyearreturn=0;
                        $twoyearreturn=0;
                        $threeyearreturn=0;
                        $fouryearreturn=0;
                        $fiveyearreturn=0;

                        $onemonthreturn=$this->calcreturnFund($name,$date,1);
                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnFund($name,$date,3);
                        $sixmonthreturn=$this->calcreturnFund($name,$date,6);
                        $oneyearreturn=$this->calcreturnFund($name,$date,12);
                        $twoyearreturn=$this->calcreturnFund($name,$date,24);
                        $threeyearreturn=$this->calcreturnFund($name,$date,36);
                        $fouryearreturn=$this-> calcreturnFund($name,$date,48);
                        $fiveyearreturn=$this-> calcreturnFund($name,$date,60);
                        $sinceinception=$this->calcreturnFund($name,$date,'sinceinception');
                        $sinceinceptiona=$this->calcreturnFund($name,$date,'sinceinceptiona');

                        $sqlupdate="update managedfunds set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',fiveyearreturn='".$fiveyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and fundname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }

                }

                //calculation for modelportfolio
                $portname='';
                foreach($portfolioNameDate as $portnd)
                {
                    $n=0;
                    foreach($portnd as $pnd)
                    {
                        if($n==0)
                        {
                            $portname=$pnd;
                        }
                        else
                        {
                            $this->calculation($portname,$pnd);
                        }
                        $n++;
                    }
                }
                foreach($rpmNameDate as $portnd)
                {
                    $n=0;
                    foreach($portnd as $pnd)
                    {
                        if($n==0)
                        {
                            $portname=$pnd;
                        }
                        else
                        {
                            $this->calculationrpm($portname,$pnd);
                        }
                        $n++;
                    }
                }

            }
            //calculations for index
            else if ($calculationType=="Index")
            {

                //echo "namearray:".$arraylength." ";
                for($n=0;$n<sizeof($nameArray);$n++)
                {
                    $resultbarray=array();
                    $name=$nameArray[$n];
                    $date=$dateArray[$n];
                    $sqlbefore="select indicesid from indices where date<'".$date."' and indexname='".$name."'";
                    $resultbefore=mysqli_query($con,$sqlbefore);
                    foreach ($resultbefore as $rb)
                    {
                        foreach ($rb as $r)
                        {
                            array_push($resultbarray,$r);
                        }
                    }
                    $numberbefore=sizeof($resultbarray);
                    //calc 1 month return
                    if($numberbefore==0){}
                    else if($numberbefore<2)
                    {
                        $onemonthreturn=$this->calcreturnIndex($name,$date,1);
                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $sinceinception=$this->calcreturnIndex($name,$date,'sinceinception');
                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3 months
                    else if ($numberbefore<5)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;

                        $onemonthreturn=$this->calcreturnIndex($name,$date,1);
                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnIndex($name,$date,3);
                        $sinceinception=$this->calcreturnIndex($name,$date,'sinceinception');

                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months
                    else if ($numberbefore<11)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;

                        $onemonthreturn=$this->calcreturnIndex($name,$date,1);
                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnIndex($name,$date,3);
                        $sixmonthreturn=$this->calcreturnIndex($name,$date,6);
                        $sinceinception=$this->calcreturnIndex($name,$date,'sinceinception');

                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months and 1 year
                    else if ($numberbefore<23)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;
                        $oneyearreturn=0;

                        $onemonthreturn=$this->calcreturnIndex($name,$date,1);
                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnIndex($name,$date,3);
                        $sixmonthreturn=$this->calcreturnIndex($name,$date,6);
                        $oneyearreturn=$this->calcreturnIndex($name,$date,12);
                        $sinceinception=$this->calcreturnIndex($name,$date,'sinceinception');
                        $sinceinceptiona=$this->calcreturnIndex($name,$date,'sinceinceptiona');

                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months and 1,2 year
                    else if ($numberbefore<35)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;
                        $oneyearreturn=0;
                        $twoyearreturn=0;

                        $onemonthreturn=$this->calcreturnIndex($name,$date,1);
                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnIndex($name,$date,3);
                        $sixmonthreturn=$this->calcreturnIndex($name,$date,6);
                        $oneyearreturn=$this->calcreturnIndex($name,$date,12);
                        $twoyearreturn=$this->calcreturnIndex($name,$date,24);
                        $sinceinception=$this->calcreturnIndex($name,$date,'sinceinception');
                        $sinceinceptiona=$this->calcreturnIndex($name,$date,'sinceinceptiona');

                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months and 1,2,3 year
                    else if ($numberbefore<47)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;
                        $oneyearreturn=0;
                        $twoyearreturn=0;
                        $threeyearreturn=0;

                        $onemonthreturn=$this->calcreturnIndex($name,$date,1);
                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnIndex($name,$date,3);
                        $sixmonthreturn=$this->calcreturnIndex($name,$date,6);
                        $oneyearreturn=$this->calcreturnIndex($name,$date,12);
                        $twoyearreturn=$this->calcreturnIndex($name,$date,24);
                        $threeyearreturn=$this->calcreturnIndex($name,$date,36);
                        $sinceinception=$this->calcreturnIndex($name,$date,'sinceinception');
                        $sinceinceptiona=$this->calcreturnIndex($name,$date,'sinceinceptiona');

                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months and 1,2,3,4 year
                    else if ($numberbefore<59)
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;
                        $oneyearreturn=0;
                        $twoyearreturn=0;
                        $threeyearreturn=0;
                        $fouryearreturn=0;

                        $onemonthreturn=$this->calcreturnIndex($name,$date,1);
                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnIndex($name,$date,3);
                        $sixmonthreturn=$this->calcreturnIndex($name,$date,6);
                        $oneyearreturn=$this->calcreturnIndex($name,$date,12);
                        $twoyearreturn=$this->calcreturnIndex($name,$date,24);
                        $threeyearreturn=$this->calcreturnIndex($name,$date,36);
                        $fouryearreturn=$this-> calcreturnIndex($name,$date,48);
                        $sinceinception=$this->calcreturnIndex($name,$date,'sinceinception');
                        $sinceinceptiona=$this->calcreturnIndex($name,$date,'sinceinceptiona');

                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }
                    //calc 1,3,6 months and 1,2,3,4,5 year
                    else
                    {
                        $onemonthreturn=0;
                        $threemonthreturn=0;
                        $sixmonthreturn=0;
                        $oneyearreturn=0;
                        $twoyearreturn=0;
                        $threeyearreturn=0;
                        $fouryearreturn=0;
                        $fiveyearreturn=0;

                        $onemonthreturn=$this->calcreturnIndex($name,$date,1);
                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                        $threemonthreturn=$this->calcreturnIndex($name,$date,3);
                        $sixmonthreturn=$this->calcreturnIndex($name,$date,6);
                        $oneyearreturn=$this->calcreturnIndex($name,$date,12);
                        $twoyearreturn=$this->calcreturnIndex($name,$date,24);
                        $threeyearreturn=$this->calcreturnIndex($name,$date,36);
                        $fouryearreturn=$this-> calcreturnIndex($name,$date,48);
                        $fiveyearreturn=$this-> calcreturnIndex($name,$date,60);
                        $sinceinception=$this->calcreturnIndex($name,$date,'sinceinception');
                        $sinceinceptiona=$this->calcreturnIndex($name,$date,'sinceinceptiona');

                        $sqlupdate="update indices set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',fiveyearreturn='".$fiveyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and indexname='".$name."'";
                        mysqli_query($con,$sqlupdate);
                    }

                }

                //calculation for modelportfolio
                $portname='';
                foreach($portfolioNameDate as $portnd)
                {
                    $n=0;
                    foreach($portnd as $pnd)
                    {
                        if($n==0)
                        {
                            $portname=$pnd;
                        }
                        else
                        {
                            $this->calculation($portname,$pnd);
                        }
                        $n++;
                    }
                }

            }
    }

    //this function is used to calculate returns for a single portfolio record
private function calculation($name,$date)//$name is portfolio name 
{
    include("Component/connection.php");
    $dateExplode = explode('-',$date);
    $year=$dateExplode[0];
    $month=$dateExplode[1];
    $fundlist=array();
    $indexlist=array();
    $fundweightinglist=array();
    $indexweightinglist=array();

    //get fund and index info for portfolio
    $sql2="select fundslist,fweightinglist,indexlist,iweightinglist from modelportfolios where portfolioname='".$name."' and year(date)='".$year."' and month(date)='".$month."'";
    $result=mysqli_query($con,$sql2);
    $x=1;
    foreach($result as $resul)
    {
        foreach($resul as $re)
        {
            if($x==1)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($fundlist,$exploded);
                }
            }
            else if ($x==2)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($fundweightinglist,$exploded);
                }
            }
            else if ($x==3)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($indexlist,$exploded);
                }
            }
            else if ($x==4)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($indexweightinglist,$exploded);
                }
            }
            $x++;
        }
    }


    $sql="select PortfoliosID from modelportfolios where date<'".$date."' and portfolioname='".$name."'";
    $result=mysqli_query($con,$sql);
    $resultarray=array();
    foreach($result as $resul)
    {
        //$numberbefore=sizeof($resul);
        foreach ($resul as $r)
        {
            array_push($resultarray, $r);
        }
    }
    $numberbefore=sizeof($resultarray);
    //calc 1 month return
    if($numberbefore<2 && $numberbefore>=0)
    {
        $onemonthreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3 months
    else if ($numberbefore<5)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months
    else if ($numberbefore<11)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1 year
    else if ($numberbefore<23)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $oneyearreturn=$this->calcreturn($name,$date,12);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturn($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2 year
    else if ($numberbefore<35)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $oneyearreturn=$this->calcreturn($name,$date,12);
        $twoyearreturn=$this->calcreturn($name,$date,24);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturn($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3 year
    else if ($numberbefore<47)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $oneyearreturn=$this->calcreturn($name,$date,12);
        $twoyearreturn=$this->calcreturn($name,$date,24);
        $threeyearreturn=$this->calcreturn($name,$date,36);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturn($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3,4 year
    else if ($numberbefore<59)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;
        $fouryearreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $oneyearreturn=$this->calcreturn($name,$date,12);
        $twoyearreturn=$this->calcreturn($name,$date,24);
        $threeyearreturn=$this->calcreturn($name,$date,36);
        $fouryearreturn=$this-> calcreturn($name,$date,48);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturn($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3,4,5 year
    else if ($numberbefore<71)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;
        $fouryearreturn=0;
        $fiveyearreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $oneyearreturn=$this->calcreturn($name,$date,12);
        $twoyearreturn=$this->calcreturn($name,$date,24);
        $threeyearreturn=$this->calcreturn($name,$date,36);
        $fouryearreturn=$this-> calcreturn($name,$date,48);
        $fiveyearreturn=$this-> calcreturn($name,$date,60);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturn($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',fiveyearreturn='".$fiveyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
}

private function calcreturn($name,$date,$limit)
{   
    include("Component/connection.php");
    if($limit == 1)
    {
        $sql3="select fundslist from modelportfolios where portfolioname ='".$name."' and date ='".$date."' order by date asc limit 1";
        $temp=mysqli_query($con,$sql3);
        $return=1;
        $fundarray=array();
        $fundweightingarray=array();
        $fundvaluearray=array();
        foreach ($temp as $tem)
        {
            foreach($tem as $t)
            {
                $fundExplode = explode(',',$t);
                foreach($fundExplode as $fe)
                {
                    array_push($fundarray, $fe);
                }
            }
        }
        for($n=0;$n<sizeof($fundarray);$n++)
        {
            $sql4="select onemonthreturn from managedfunds where fundname='".$fundarray[$n]."' and year(date)=year('".$date."') and month(date)=month('".$date."')";
            $result4=mysqli_query($con,$sql4);
            foreach($result4 as $resul)
            {
                foreach($resul as $t)
                {
                    array_push($fundvaluearray,$t);
                }
            }
        }
        $sql5="select fweightinglist from modelportfolios where portfolioname ='".$name."' and date ='".$date."' order by date asc limit 1";

        $result5=mysqli_query($con,$sql5);
        foreach($result5 as $resul)
        {
            foreach($resul as $r)
            {
                $weightingExplode=explode(',',$r);
                foreach($weightingExplode as $t)
                {
                    array_push($fundweightingarray,$t);
                }
            }
        }
        for($m=0;$m<sizeof($fundvaluearray);$m++)
        {
            $return+=$fundvaluearray[$m]*$fundweightingarray[$m];
        }   
        $return-=1;
        return $return;
    }
    else if ($limit != 'sinceinception' && $limit != 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and date<='".$date."' order by date desc limit ".$limit;
        $temp=mysqli_query($con,$sql3);
        $return=1;

        $counter=0;
        foreach($temp as $tem)
        {
            foreach ($tem as $t)
            {
                $return=$return*($t+1);
            }
        }

        if($counter>12)
        {
            $exp=12/$counter;

            $return=pow($return,$exp)-1;
        }
        else
        {
            $return-=1;
        }
        return $return;
        
    }
    else if ($limit == 'sinceinception')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }
        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }
        for($n=0;$n<sizeof($returnarray);$n++)
        {
            $return=$return*(1+$returnarray[$n]);
        }
        $return-=1;

        return $return;
    }
    else if ($limit == 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }

        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }

        if(sizeof($returnarray)>11)
        {
            for($n=0;$n<sizeof($returnarray);$n++)
            {
                $return=$return*(1+$returnarray[$n]);
        }

            $exp=12/sizeof($returnarray);

            $return=pow($return,$exp)-1;
        }
        return $return;
    }
}

private function calcreturnFund($name,$date,$limit)
{
    include("Component/connection.php");
    if($limit == 1)
    {
        $return=0;
        $values=array();
        $sqlone="select value from managedfunds where fundname='".$name."' and date<='".$date."' order by date desc limit 2";
        $res=mysqli_query($con,$sqlone);
        foreach($res as $re)
            {
            foreach ($re as $r)
            {
                array_push($values,$r);
            }
        }
        $current=$values[0];
        $pre=$values[1];
        $return=$current/$pre-1;
        return $return;
    }
    else if ($limit != 'sinceinception' && $limit != 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from managedfunds where fundname ='".$name."' and date<='".$date."' order by date desc limit ".$limit;
        $temp=mysqli_query($con,$sql3);
        $return=1;

        $counter=0;
        foreach($temp as $tem)
        {
            foreach ($tem as $t)
            {
                $return=$return*($t+1);
                $counter++;
            }
        }
        if($counter>12)
        {
            $exp=12/$counter;

            $return=pow($return,$exp)-1;
        }
        else
        {
            $return-=1;
        }
        return $return;
        
    }
    else if ($limit == 'sinceinception')
    {
        $sql3="select onemonthreturn from managedfunds where fundname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from managedfunds where fundname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }
        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }
        for($n=0;$n<sizeof($returnarray);$n++)
        {
            $return=$return*(1+$returnarray[$n]);
        }
        $return-=1;

        return $return;
    }
    else if ($limit == 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from managedfunds where fundname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from managedfunds where fundname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }

        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }

        if(sizeof($returnarray)>11)
        {
            for($n=0;$n<sizeof($returnarray);$n++)
            {
                $return=$return*(1+$returnarray[$n]);
            }

            $exp=12/sizeof($returnarray);

            $return=pow($return,$exp)-1;
        }
        return $return;
    }

}

private function calcreturnIndex($name,$date,$limit)
{

    include("Component/connection.php");
    if($limit == 1)
    {
        $return=0;
        $values=array();
        $sqlone="select value from indices where indexname='".$name."' and date<='".$date."' order by date desc limit 2";
        $res=mysqli_query($con,$sqlone);
        foreach($res as $re)
            {
            foreach ($re as $r)
            {
                array_push($values,$r);
            }
        }
        $current=$values[0];
        $pre=$values[1];
        $return=$current/$pre-1;
        return $return;
    }
    else if ($limit != 'sinceinception' && $limit != 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from indices where indexname ='".$name."' and date<='".$date."' order by date desc limit ".$limit;
        $temp=mysqli_query($con,$sql3);
        $return=1;

        $counter=0;
        foreach($temp as $tem)
        {
            foreach ($tem as $t)
            {
                $return=$return*($t+1);
                $counter++;
            }
        }
        if($counter>12)
        {
            $exp=12/$counter;

            $return=pow($return,$exp)-1;
        }
        else
        {
            $return-=1;
        }
        return $return;
        
    }
    else if ($limit == 'sinceinception')
    {
        $sql3="select onemonthreturn from indices where indexname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from indices where indexname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }
        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }
        for($n=0;$n<sizeof($returnarray);$n++)
        {
            $return=$return*(1+$returnarray[$n]);
        }
        $return-=1;

        return $return;
    }
    else if ($limit == 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from indices where indexname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from indices where indexname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }

        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }

        if(sizeof($returnarray)>11)
        {
            for($n=0;$n<sizeof($returnarray);$n++)
            {
                $return=$return*(1+$returnarray[$n]);
        }

            $exp=12/sizeof($returnarray);

            $return=pow($return,$exp)-1;
        }
        return $return;
    }

}

private function calculationrpm($name,$date)//$name is portfolio name 
{
    include("Component/connection.php");
    $dateExplode = explode('-',$date);
    $year=$dateExplode[0];
    $month=$dateExplode[1];
    $fundlist=array();
    $indexlist=array();
    $fundweightinglist=array();
    $indexweightinglist=array();

    //get fund and index info for portfolio
    $sql2="select fundslist,fweightinglist,indexlist,iweightinglist from modelportfolios where portfolioname='".$name."' and year(date)='".$year."' and month(date)='".$month."'";
    $result=mysqli_query($con,$sql2);
    $x=1;
    foreach($result as $resul)
    {
        foreach($resul as $re)
        {
            if($x==1)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($fundlist,$exploded);
                }
            }
            else if ($x==2)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($fundweightinglist,$exploded);
                }
            }
            else if ($x==3)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($indexlist,$exploded);
                }
            }
            else if ($x==4)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($indexweightinglist,$exploded);
                }
            }
            $x++;
        }
    }


    $sql="select PortfoliosID from modelportfolios where date<'".$date."' and portfolioname='".$name."'";
    $result=mysqli_query($con,$sql);
    $resultarray=array();
    foreach($result as $resul)
    {
        //$numberbefore=sizeof($resul);
        foreach ($resul as $r)
        {
            array_push($resultarray, $r);
        }
    }
    $numberbefore=sizeof($resultarray);
    //calc 1 month return
    if($numberbefore<2 && $numberbefore>=0)
    {
        $onemonthreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3 months
    else if ($numberbefore<5)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months
    else if ($numberbefore<11)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1 year
    else if ($numberbefore<23)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $oneyearreturn=$this->calcreturnrpm($name,$date,12);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturnrpm($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2 year
    else if ($numberbefore<35)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $oneyearreturn=$this->calcreturnrpm($name,$date,12);
        $twoyearreturn=$this->calcreturnrpm($name,$date,24);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturnrpm($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3 year
    else if ($numberbefore<47)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $oneyearreturn=$this->calcreturnrpm($name,$date,12);
        $twoyearreturn=$this->calcreturnrpm($name,$date,24);
        $threeyearreturn=$this->calcreturnrpm($name,$date,36);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturnrpm($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3,4 year
    else if ($numberbefore<59)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;
        $fouryearreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $oneyearreturn=$this->calcreturnrpm($name,$date,12);
        $twoyearreturn=$this->calcreturnrpm($name,$date,24);
        $threeyearreturn=$this->calcreturnrpm($name,$date,36);
        $fouryearreturn=$this-> calcreturnrpm($name,$date,48);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturnrpm($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3,4,5 year
    else if ($numberbefore<71)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;
        $fouryearreturn=0;
        $fiveyearreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $oneyearreturn=$this->calcreturnrpm($name,$date,12);
        $twoyearreturn=$this->calcreturnrpm($name,$date,24);
        $threeyearreturn=$this->calcreturnrpm($name,$date,36);
        $fouryearreturn=$this-> calcreturnrpm($name,$date,48);
        $fiveyearreturn=$this-> calcreturnrpm($name,$date,60);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturnrpm($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',fiveyearreturn='".$fiveyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
}

private function calcreturnrpm($name,$date,$limit)
{   
    include("Component/connection.php");
    if($limit == 1)
    {
        $sql3="select fundslist from modelportfolios where portfolioname ='".$name."' and date ='".$date."' order by date asc limit 1";
        $temp=mysqli_query($con,$sql3);
        $return=1;
        $fundarray=array();
        $fundweightingarray=array();
        $fundvaluearray=array();
        foreach ($temp as $tem)
        {
            foreach($tem as $t)
            {
                $fundExplode = explode(',',$t);
                foreach($fundExplode as $fe)
                {
                    array_push($fundarray, $fe);
                }
            }
        }
        for($n=0;$n<sizeof($fundarray);$n++)
        {
            $sql4="select onemonthreturn from modelportfolios where portfolioname='".$fundarray[$n]."' and year(date)=year('".$date."') and month(date)=month('".$date."')";
            $result4=mysqli_query($con,$sql4);
            foreach($result4 as $resul)
            {
                foreach($resul as $t)
                {
                    array_push($fundvaluearray,$t);
                }
            }
        }
        $sql5="select fweightinglist from modelportfolios where portfolioname ='".$name."' and date ='".$date."' order by date asc limit 1";

        $result5=mysqli_query($con,$sql5);
        foreach($result5 as $resul)
        {
            foreach($resul as $r)
            {
                $weightingExplode=explode(',',$r);
                foreach($weightingExplode as $t)
                {
                    array_push($fundweightingarray,$t);
                }
            }
        }
        for($m=0;$m<sizeof($fundvaluearray);$m++)
        {
            $return+=$fundvaluearray[$m]*$fundweightingarray[$m];
        }   
        $return-=1;
        return $return;
    }
    else if ($limit != 'sinceinception' && $limit != 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and date<='".$date."' order by date desc limit ".$limit;
        $temp=mysqli_query($con,$sql3);
        $return=1;

        $counter=0;
        foreach($temp as $tem)
        {
            foreach ($tem as $t)
            {
                $return=$return*($t+1);
            }
        }

        if($counter>12)
        {
            $exp=12/$counter;

            $return=pow($return,$exp)-1;
        }
        else
        {
            $return-=1;
        }
        return $return;
        
    }
    else if ($limit == 'sinceinception')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }
        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }
        for($n=0;$n<sizeof($returnarray);$n++)
        {
            $return=$return*(1+$returnarray[$n]);
        }
        $return-=1;

        return $return;
    }
    else if ($limit == 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }

        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }

        if(sizeof($returnarray)>11)
        {
            for($n=0;$n<sizeof($returnarray);$n++)
            {
                $return=$return*(1+$returnarray[$n]);
        }

            $exp=12/sizeof($returnarray);

            $return=pow($return,$exp)-1;
        }
        return $return;
    }
}
}
?>